package com.lntea.jdbc.spring;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringRunner;

import com.lntea.jdbc.spring.bean.GoodsStock;

@RunWith(SpringRunner.class)
@ContextConfiguration(locations={"classpath:com/lntea/jdbc/spring/jdbc-template.xml"})
public class SimpleJdbcInsertTest {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	// 使用SimpleJdbcInsert和Map
	@Test
	public void testInsertWithMap(){
		SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate)
				.withTableName("t_wms_goods_stock");
		
		Map<String, Object> params = new HashMap<>();
		params.put("id", 3);
		params.put("warehouse_id", 1);
		params.put("amount", 15d);
		int insertCount = insert.execute(params);
		System.out.println("insertCount:" + insertCount);
	}
	
	// 使用SimpleJdbcInsert和Map，主键自增
	@Test
	public void testInsertWithMapAndGeneratedKey(){
		SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate)
				.withTableName("t_wms_goods_stock")
				.usingGeneratedKeyColumns("id");
		
		Map<String, Object> params = new HashMap<>();
		params.put("warehouse_id", 1);
		params.put("amount", 32d);
		Number newId = insert.executeAndReturnKey(params);
		System.out.println("insert new id:" + newId);
	}
	
	// 使用SimpleJdbcInsert，指定要插入的column
	@Test
	public void testInsertWithSpecifyingColumn(){
		SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate)
				.withTableName("t_wms_goods_stock")
				.usingGeneratedKeyColumns("id")
				.usingColumns("warehouse_id", "amount");
		
		Map<String, Object> params = new HashMap<>();
		params.put("warehouse_id", 1);
		params.put("amount", 80d);
		Number newId = insert.executeAndReturnKey(params);
		System.out.println("insert new id:" + newId);
	}
	
	
	// 使用BeanPropertySqlParameterSource
	@Test
	public void testBeanPropertySqlParameterSource(){
		SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate)
				.withTableName("t_wms_goods_stock")
				.usingGeneratedKeyColumns("id");
		
		SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(new GoodsStock(1l, 23d));
		Number newId = insert.executeAndReturnKey(parameterSource);
		System.out.println("insert new id:" + newId);
	}
	
	// 使用MapSqlParameterSource 
	@Test
	public void testMapSqlParameterSource(){
		SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate)
				.withTableName("t_wms_goods_stock")
				.usingGeneratedKeyColumns("id");
		
		SqlParameterSource parameterSource = new MapSqlParameterSource()
				.addValue("warehouse_id", 1l)
				.addValue("amount", 23d);
		Number newId = insert.executeAndReturnKey(parameterSource);
		System.out.println("insert new id:" + newId);
	}
	
}
